﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;


namespace Sequence
{
    class CSqlString
    {
        public static string GetQuerydef(string sDBMS, string sQueryName)
        {
            string sRet = "";
            if (sDBMS == "OCI")
                sRet = "select id,query_name from t_api_querydef where lower(query_name) like '%" + sQueryName + "%'  and rownum < 100";
            else if (sDBMS == "MSS")
                sRet = "select top 1000 id,query_name from t_api_querydef where lower(query_name) like '%" + sQueryName + "%' ";
            else if (sDBMS == "XLS")
                sRet = "select top 1000 id,query_name from [t_api_querydef$] where lower(query_name) like '%" + sQueryName + "%' ";
            return sRet;
        }

        public static string GetConnectionType(string sConnString)
        {
            //Example Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Sapiens\Sequence\Sequence.xls;Extended Properties="Excel 8.0; HDR = YES;"
            string sRet = "ORA";
            if ( sConnString.IndexOf("Microsoft.Jet.") > 0) 
                sRet = "XLS";
            else if ( sConnString.IndexOf("oracle") > 0) 
                sRet = "ORA";
            else if (sConnString.IndexOf("Microsoft.ACE") > 0)
                sRet = "ACC";
            else
                sRet = "MSS";
            return sRet;
        }

        public static string GetProcess(string sDBMS,string sName)
        {
            string sRet = "";
            if (sDBMS == "OCI")
                sRet = "select * from t_api_qd_process where NAME like '$Name$%' and rownum < 100 order by NAME";
            else if (sDBMS == "MSS")
                sRet = "select * from t_api_qd_process where NAME like '$Name$%' and rownum < 100 order by NAME";
            else if (sDBMS == "ACC")
                sRet = "select top 100 * from [t_api_qd_process] where NAME  like '$Name$%' order by NAME";
            else if (sDBMS == "XLS")
                sRet = "select * from [t_api_qd_process$] where NAME  like '$Name$%' and rownum < 100 order by NAME";
            sRet = sRet.Replace("$Name$", sName);
            return sRet;
        }


        public static string TestType(string sSql, string sDBMS)
        {

            if (sDBMS == "XLS")
            {
                sSql = sSql.Replace("t_api_qd_process_step_params.", "[t_api_qd_process_step_params$].");
                sSql = sSql.Replace("t_api_qd_process_step_params ", "[t_api_qd_process_step_params$] ");
                sSql = sSql.Replace("t_api_qd_process_step.", "[t_api_qd_process_step$].");
                sSql = sSql.Replace("t_api_qd_process.", "[t_api_qd_process$].");
                sSql = sSql.Replace("t_api_qd_process_step ", "[t_api_qd_process_step$] ");
                sSql = sSql.Replace("t_api_qd_process ", "[t_api_qd_process$] ");
            }
            return sSql;
        }

        public static string GetStepsById(string sDBMS, string sId)
        {
            string sRet = "";
            sRet = @"
select 
  t_api_qd_process.name as process, 
  t_api_qd_process_step.ID,
  t_api_qd_process_step.PROCESS_ID,
  t_api_qd_process_step.NAME,
  t_api_qd_process_step.QUERY_NAME,
  t_api_qd_process_step.STEP_MODE, 
  t_api_qd_process_step.TYPE sType
from 
  t_api_qd_process 
  left outer join t_api_qd_process_step on  t_api_qd_process.id =  t_api_qd_process_step.process_id 
where 
  t_api_qd_process.id=$sId$
order by 
  t_api_qd_process_step.ID";


            switch (sDBMS)
            {
                case "MSS":
                case "ORA":
                    sRet = sRet.Replace("$sId$", sId);
                    break;
                case "XLS":
                    sRet = sRet.Replace("$sId$", sId);
                    sRet = sRet.Replace("t_api_qd_process_step_params.", "[t_api_qd_process_step_params$].");
                    sRet = sRet.Replace("t_api_qd_process_step_params ", "[t_api_qd_process_step_params$] ");
                    sRet = sRet.Replace("t_api_qd_process_step.", "[t_api_qd_process_step$].");
                    sRet = sRet.Replace("t_api_qd_process.", "[t_api_qd_process$].");
                    sRet = sRet.Replace("t_api_qd_process_step ", "[t_api_qd_process_step$] ");
                    sRet = sRet.Replace("t_api_qd_process ", "[t_api_qd_process$] ");
                    break;
            }            
            return sRet;
        }           

        public static string GetQuerydefProcess(string sDBMS, string sQueryName)
        {
            string sRet = "";
            if (sDBMS == "OCI")
                sRet = "select id,name from t_api_qd_process where lower(name) like '%" + sQueryName + "%'  and rownum < 100";
            else if (sDBMS == "MSS")
                sRet = "select top 1000 id,name from t_api_qd_process where lower(name) like '%" + sQueryName + "%' ";
            else if (sDBMS == "XLS")
                sRet = "select top 1000 id,name from [t_api_qd_process$] where lower(name) like '%" + sQueryName + "%' ";
            return sRet;
        }

        public static string RunSql(string sSql, DbConnection oConnection)
        {
            string sRet = "";
            try
            {
                DbCommand command = oConnection.CreateCommand();
                command.CommandText = sSql;
                command.CommandType = CommandType.Text;
                int iRows = command.ExecuteNonQuery();
                command.Dispose();

            }
            catch (Exception ex)
            {
                sRet  = ex.Message + " (" + sSql + ")\r\n";
            }
            return sRet;
        }
    }
}
